import pymysql


# 数据库操作工具类
class DbUtils():

    def __init__(self,db):# db 要操作的数据库的名称
        self.db = db

    # 获取连接函数
    def getConnect(self):
        self.conn = pymysql.connect(user="root",password="root",host="127.0.0.1",port=3306,database=self.db)
        return self.conn

    # 定义一个查询函数
    def selectAll(self,sql,params=None):

        if params == None:
            params = []

        global conn,cursor,result

        try:
            # 获取连接对象
            conn = self.getConnect()
            # 获取游标对象
            cursor = conn.cursor()
            # 执行sql
            n = cursor.execute(sql,params)
            # 获取所有的数据
            result = cursor.fetchall()
        except Exception as e:
            print(e)
        finally:
            # 释放资源
            cursor.close()
            conn.close()

        return result

    # 查询单个
    def selectOne(self,sql,params=None):
        if params == None:
            params = []
        global conn, cursor, result

        try:
            # 获取连接对象
            conn = self.getConnect()
            # 获取游标对象
            cursor = conn.cursor()
            # 执行sql
            n = cursor.execute(sql,params)
            # 获取所有的数据
            result = cursor.fetchone()
        except Exception as e:
            print(e)
        finally:
            # 释放资源
            cursor.close()
            conn.close()

        return result

    # 添加 修改 删除数据
    def updateData(self,sql,params=[]):
        n = 0
        conn = None
        cursor = None
        try:
            # 获取连接
            conn = self.getConnect()
            # 获取游标
            cursor = conn.cursor()
            # 执行sql
            n  = cursor.execute(sql,params)
            # 提交事务
            conn.commit()
        except Exception as e:
            print(e)
        finally:
            cursor.close()
            conn.close()
        return n




if __name__ == '__main__':
    # 获取工具类对象
    utils = DbUtils("shop")
    # 查询所有的商品数据
    sql = "select * from product"
    products = utils.selectAll(sql)
    # print(products)
    # 查询id为1的商品
    sql2 = "select * from product where pid =1"
    product = utils.selectOne(sql2)
    print(product)
    # 修改商品数据 修改id为1的商品的名称为外套
    sql3 = "update product set pname = '外套' where pid =1 "
    utils.updateData(sql3)

